﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Dosimetria.Models.DataAnnotations_Classes;
using System.Data;
using Dosimetria.Models;


namespace Dosimetria.DAL
{
    public class Querys
    {
        #region consultas de selección
        /// <summary>
        /// Lista todos los tipos de dosimetros
        /// </summary>
        /// <returns>List<TipoDosimetro></returns>
        public List<TipoDosimetro> getAllTiposDosimetros()
        {
            string query = "SELECT * FROM tipos_dosimetros";
            DataTable tabla = Conexion.getSelectTable(query);

            List<TipoDosimetro> lTiposDosimetros = new List<TipoDosimetro>();
            foreach (DataRow fila in tabla.Rows)
            {
                TipoDosimetro tDosim = new TipoDosimetro { id = Convert.ToInt32(fila["id"]), tipo = fila["tipo"].ToString() };
                lTiposDosimetros.Add(tDosim);
            }
            return lTiposDosimetros;
        }

        public int getCantDosimetroMostrar(string tipoDosimetro)
        {
            string query = "SELECT COUNT(*) cant from HCD_dosimetros HCD_DOSIM " +
                            "WHERE HCD_DOSIM.devuelto = '1' " +
                            "AND HCD_DOSIM.daniado = '0' " +
                            "AND HCD_DOSIM.extraviado = '0' " +
                            "AND HCD_DOSIM.generadoTXT = 0 " +
                            "AND HCD_DOSIM.DOSIM_TIPO = @tipoDosimetro ";
            DataTable tabla = Conexion.getSelectTable(query, tipoDosimetro);
            int cantidad = 0;
            foreach(DataRow fila in tabla.Rows)
            {
                cantidad = Convert.ToInt32(fila["cant"]);
            }
            return cantidad;
        }

        public int getCantDosimetrosRecibidosByTipoDosimetro(string tipoDosimetro)
        {
            string query = "SELECT	COUNT(HCD_DOSIM.DFUN_NUM) " +
                            "FROM HCD_dosimetros HCD_DOSIM " +
                            "INNER JOIN FUNCIONARIO F ON F.FUNC_COD = HCD_DOSIM.FUNC_COD " +
                            "INNER JOIN DOSIM_FUN DF ON DF.DFUN_COD = HCD_DOSIM.DFUN_COD " +
                            "WHERE HCD_DOSIM.devuelto = '1' " +
                            "AND HCD_DOSIM.daniado = '0' " +
                            "AND HCD_DOSIM.extraviado = '0' " +
                            "AND F.FUNC_VIGENTE = 'S' " +
                            "AND HCD_DOSIM.generadoTXT = 0 " +
                            "AND HCD_DOSIM.DOSIM_TIPO = @tipoDosimetro AND HCD_DOSIM.DFUN_NUM NOT LIKE '333%'";
            DataTable tabla = Conexion.getSelectTable(query, tipoDosimetro);

            int cantidad = 0;
            foreach (DataRow fila in tabla.Rows)
            {
                cantidad = Convert.ToInt32(fila["cant"]);
            }
            return cantidad;
        }

        public int getCantPD5Recibidos()
        {
            string query = "SELECT	COUNT(HCD_DOSIM.DFUN_NUM) cant " +
                           "FROM HCD_dosimetros HCD_DOSIM " +
                          "INNER JOIN FUNCIONARIO F ON F.FUNC_COD = HCD_DOSIM.FUNC_COD " +
                          "INNER JOIN DOSIM_FUN DF ON DF.DFUN_COD = HCD_DOSIM.DFUN_COD " +
                          "INNER JOIN DOSIM_TARJETA DT ON DT.DT_DFUN_COD = DF.DFUN_COD " +
                          "WHERE HCD_DOSIM.devuelto = '1' " +
                          "AND HCD_DOSIM.daniado = '0' " +
                          "AND HCD_DOSIM.extraviado = '0' " +
                          "AND F.FUNC_VIGENTE = 'S' " +
                          "AND HCD_DOSIM.generadoTXT = 0 " +
                          "AND HCD_DOSIM.DOSIM_TIPO = 'PD5' AND HCD_DOSIM.DFUN_NUM NOT LIKE '333%'  ";
            DataTable tabla = Conexion.getSelectTable(query);
            int cantidad = 0;
            foreach (DataRow fila in tabla.Rows)
            {
                cantidad = Convert.ToInt32(fila["cant"]);
            }
            return cantidad;
        }

        public int getCantDosimetrosRecibidosStartBy333()
        {
            string query = "SELECT	COUNT(HCD_DOSIM.DFUN_NUM) cant " +
                           "FROM HCD_dosimetros HCD_DOSIM " +
                           "INNER JOIN FUNCIONARIO F ON F.FUNC_COD = HCD_DOSIM.FUNC_COD " +
                           "INNER JOIN DOSIM_FUN DF ON DF.DFUN_COD = HCD_DOSIM.DFUN_COD " +
                           "INNER JOIN DOSIM_TARJETA DT ON DT.DT_DFUN_COD = DF.DFUN_COD " +
                           "WHERE HCD_DOSIM.devuelto = '1' " +
                           "AND HCD_DOSIM.daniado = '0' " +
                           "AND HCD_DOSIM.extraviado = '0' " +
                           "AND F.FUNC_VIGENTE = 'S' " +
                           "AND HCD_DOSIM.generadoTXT = 0 " +
                           "AND HCD_DOSIM.DFUN_NUM LIKE '333%' ";
            DataTable tabla = Conexion.getSelectTable(query);
            int cantidad = 0;
            foreach (DataRow fila in tabla.Rows)
            {
                cantidad = Convert.ToInt32(fila["cant"]);
            }
            return cantidad;
        }

        public List<Dosimetro> getDosimetrosRecibidosStartBy333()
        {
            string query = "SELECT	HCD_DOSIM.DFUN_NUM, " +
                           "HCD_DOSIM.DFUN_COD, " +
                           "DF.DFUN_FECHA, " +
                           "HCD_DOSIM.DOSIM_TIPO, " +
                           "F.FUNC_NOM, " +
                           "HCD_DOSIM.Cod_Contrato, " +
                           "HCD_DOSIM.fecha_recepcion " +
                           "FROM HCD_dosimetros HCD_DOSIM " +
                           "INNER JOIN FUNCIONARIO F ON F.FUNC_COD = HCD_DOSIM.FUNC_COD " +
                           "INNER JOIN DOSIM_FUN DF ON DF.DFUN_COD = HCD_DOSIM.DFUN_COD " +
                           "INNER JOIN DOSIM_TARJETA DT ON DT.DT_DFUN_COD = DF.DFUN_COD " +
                           "WHERE HCD_DOSIM.devuelto = '1' " +
                           "AND HCD_DOSIM.daniado = '0' " +
                           "AND HCD_DOSIM.extraviado = '0' " +
                           "AND F.FUNC_VIGENTE = 'S' " +
                           "AND HCD_DOSIM.generadoTXT = 0 " +
                           "AND HCD_DOSIM.DFUN_NUM LIKE '333%' ";
            DataTable tabla = Conexion.getSelectTable(query);

            List<Dosimetro> _lDosimetro = new List<Dosimetro>();
            foreach (DataRow fila in tabla.Rows)
            {
                Dosimetro d = new Dosimetro();
                d.codigoLogicoDosimetro = Convert.ToInt32(fila["DFUN_NUM"]);
                d.codigoDosimetro = Convert.ToInt32(fila["DFUN_COD"]);
                d.dfun_fecha = fila["DFUN_FECHA"].ToString();
                d.tipoDosimetro = fila["DOSIM_TIPO"].ToString();
                d.fechaRecepcion = fila["fecha_recepcion"].ToString();
                Contrato c = new Contrato();
                c.codigoContrato = Convert.ToInt32(fila["Cod_Contrato"]);
                d.Contrato = c;

                Funcionario f = new Funcionario();
                f.nombre = fila["FUNC_NOM"].ToString();
                d.Funcionario = f;

                _lDosimetro.Add(d);
            }
            return _lDosimetro;
        }

        public List<Dosimetro> getPD5Recibidos()
        {
            string query = "SELECT	HCD_DOSIM.DFUN_NUM, " +
                            "HCD_DOSIM.DFUN_COD, " +
                            "DF.DFUN_FECHA, " +
                            "HCD_DOSIM.DOSIM_TIPO, " +
                            "F.FUNC_NOM, " +
                            "HCD_DOSIM.Cod_Contrato, " +
                            "HCD_DOSIM.fecha_recepcion " +
                            "FROM HCD_dosimetros HCD_DOSIM " +
                            "INNER JOIN FUNCIONARIO F ON F.FUNC_COD = HCD_DOSIM.FUNC_COD " +
                            "INNER JOIN DOSIM_FUN DF ON DF.DFUN_COD = HCD_DOSIM.DFUN_COD " +
                            "INNER JOIN DOSIM_TARJETA DT ON DT.DT_DFUN_COD = DF.DFUN_COD " +
                            "WHERE HCD_DOSIM.devuelto = '1' " +
                            "AND HCD_DOSIM.daniado = '0' " +
                            "AND HCD_DOSIM.extraviado = '0' " +
                            "AND F.FUNC_VIGENTE = 'S' " +
                            "AND HCD_DOSIM.generadoTXT = 0 " +
                            "AND HCD_DOSIM.DOSIM_TIPO = 'PD5' AND HCD_DOSIM.DFUN_NUM NOT LIKE '333%'  ";
           
            DataTable tabla = Conexion.getSelectTable(query);

            List<Dosimetro> _lDosimetro = new List<Dosimetro>();
            foreach (DataRow fila in tabla.Rows)
            {
                Dosimetro d = new Dosimetro();
                d.codigoLogicoDosimetro = Convert.ToInt32(fila["DFUN_NUM"]);
                d.codigoDosimetro = Convert.ToInt32(fila["DFUN_COD"]);
                d.dfun_fecha = fila["DFUN_FECHA"].ToString();
                d.tipoDosimetro = fila["DOSIM_TIPO"].ToString();
                d.fechaRecepcion = fila["fecha_recepcion"].ToString();
                Contrato c = new Contrato();
                c.codigoContrato = Convert.ToInt32(fila["Cod_Contrato"]);
                d.Contrato = c;

                Funcionario f = new Funcionario();
                f.nombre = fila["FUNC_NOM"].ToString();
                d.Funcionario = f;

                _lDosimetro.Add(d);
            }
            return _lDosimetro;

        }

        public List<Dosimetro> getPD5FilmRecibidos()
        {
            string query = "SELECT	HCD_DOSIM.DFUN_NUM, " +
                            "HCD_DOSIM.DFUN_COD, " +
                            "DF.DFUN_FECHA, " +
                            "HCD_DOSIM.DOSIM_TIPO, " +
                            "F.FUNC_NOM, " +
                            "HCD_DOSIM.Cod_Contrato, " +
                            "HCD_DOSIM.fecha_recepcion " +
                            "FROM HCD_dosimetros HCD_DOSIM " +
                            "INNER JOIN FUNCIONARIO F ON F.FUNC_COD = HCD_DOSIM.FUNC_COD " +
                            "INNER JOIN DOSIM_FUN DF ON DF.DFUN_COD = HCD_DOSIM.DFUN_COD " +
                            "INNER JOIN DOSIM_PELICULA DP ON DP.DP_DFUN_COD = DF.DFUN_COD " +
                            "WHERE HCD_DOSIM.devuelto = '1' " +
                            "AND HCD_DOSIM.daniado = '0' " +
                            "AND HCD_DOSIM.extraviado = '0' " +
                            "AND F.FUNC_VIGENTE = 'S' " +
                            "AND HCD_DOSIM.generadoTXT = 0 " +
                            "AND HCD_DOSIM.DOSIM_TIPO = 'PD5' AND HCD_DOSIM.DFUN_NUM NOT LIKE '333%' ";
            DataTable tabla = Conexion.getSelectTable(query);

            List<Dosimetro> _lDosimetro = new List<Dosimetro>();
            foreach (DataRow fila in tabla.Rows)
            {
                Dosimetro d = new Dosimetro();
                d.codigoLogicoDosimetro = Convert.ToInt32(fila["DFUN_NUM"]);
                d.codigoDosimetro = Convert.ToInt32(fila["DFUN_COD"]);
                d.dfun_fecha = fila["DFUN_FECHA"].ToString();
                d.tipoDosimetro = fila["DOSIM_TIPO"].ToString();
                d.fechaRecepcion = fila["fecha_recepcion"].ToString();
                Contrato c = new Contrato();
                c.codigoContrato = Convert.ToInt32(fila["Cod_Contrato"]);
                d.Contrato = c;

                Funcionario f = new Funcionario();
                f.nombre = fila["FUNC_NOM"].ToString();
                d.Funcionario = f;

                _lDosimetro.Add(d);
            }
            return _lDosimetro;
        }

        public int getCantidadD3DosimetroRecibidosByTipoCristal(string tipoCristal)
        {
            string query = "SELECT	COUNT(HCD_DOSIM.DFUN_NUM) cant " +
                           "FROM HCD_dosimetros HCD_DOSIM " +
                           "INNER JOIN FUNCIONARIO F ON F.FUNC_COD = HCD_DOSIM.FUNC_COD " +
                           "INNER JOIN DOSIM_FUN DF ON DF.DFUN_COD = HCD_DOSIM.DFUN_COD " +
                           "INNER JOIN DOSIM_CRISTAL DM ON DM.DC_DFUN_COD = DF.DFUN_COD " +
                           "WHERE HCD_DOSIM.devuelto = '1' " +
                           "AND HCD_DOSIM.daniado = '0' " +
                           "AND HCD_DOSIM.extraviado = '0' " +
                           "AND F.FUNC_VIGENTE = 'S' " +
                           "AND HCD_DOSIM.generadoTXT = 0 " +
                           "AND HCD_DOSIM.DOSIM_TIPO = 'PD3' AND HCD_DOSIM.DFUN_NUM NOT LIKE '333%' ";
            if(tipoCristal.Equals("100")){
                query += "AND DM.DC_TIPO_CRIS = 100";
            } 
            else{
                query +=  "AND NOT DM.DC_TIPO_CRIS = 100";
            }
            DataTable tabla = Conexion.getSelectTable(query);
            int cantidad = 0;
            foreach(DataRow fila in tabla.Rows)
            {
                cantidad = Convert.ToInt32(fila["cant"]);
            }
            return cantidad;
        }

        public List<Dosimetro> getPD3DosimetroRecibidosByTipoCristal(string tipoCristal)
        {
            string query = "SELECT	HCD_DOSIM.DFUN_NUM, " +
                            "HCD_DOSIM.DFUN_COD, " +
                            "DF.DFUN_FECHA, " +
                            "HCD_DOSIM.DOSIM_TIPO, " +
                            "F.FUNC_NOM, " +
                            "HCD_DOSIM.Cod_Contrato, " +
                            "HCD_DOSIM.fecha_recepcion	" +
                            "FROM HCD_dosimetros HCD_DOSIM " +
                            "INNER JOIN FUNCIONARIO F ON F.FUNC_COD = HCD_DOSIM.FUNC_COD " +
                            "INNER JOIN DOSIM_FUN DF ON DF.DFUN_COD = HCD_DOSIM.DFUN_COD " +
                            "INNER JOIN DOSIM_CRISTAL DM ON DM.DC_DFUN_COD = DF.DFUN_COD " +
                            "WHERE HCD_DOSIM.devuelto = '1' " +
                            "AND HCD_DOSIM.daniado = '0' " +
                            "AND HCD_DOSIM.extraviado = '0' " +
                            "AND F.FUNC_VIGENTE = 'S' " +
                            "AND HCD_DOSIM.generadoTXT = 0 " +
                            "AND HCD_DOSIM.DOSIM_TIPO = 'PD3' AND HCD_DOSIM.DFUN_NUM NOT LIKE '333%' ";
            
            //EN caso que el tipo de cristal sea 100
            //el dosimetro sera tipo PD3
            //en caso contrario sera tipo PD3(6/7)
            if(tipoCristal.Equals("100")){
                query += "AND DM.DC_TIPO_CRIS = 100";
            } 
            else{
                query +=  "AND NOT DM.DC_TIPO_CRIS = 100";
            }
            DataTable tabla =  Conexion.getSelectTable(query);

            List<Dosimetro> _lDosimetro = new List<Dosimetro>();
            foreach (DataRow fila in tabla.Rows)
            {
                Dosimetro d = new Dosimetro();
                d.codigoLogicoDosimetro = Convert.ToInt32(fila["DFUN_NUM"]);
                d.codigoDosimetro = Convert.ToInt32(fila["DFUN_COD"]);
                d.dfun_fecha = fila["DFUN_FECHA"].ToString();
                d.tipoDosimetro = fila["DOSIM_TIPO"].ToString();
                d.fechaRecepcion = fila["fecha_recepcion"].ToString();
                Contrato c = new Contrato();
                c.codigoContrato = Convert.ToInt32(fila["Cod_Contrato"]);
                d.Contrato = c;

                Funcionario f = new Funcionario();
                f.nombre = fila["FUNC_NOM"].ToString();
                d.Funcionario = f;

                _lDosimetro.Add(d);
            }
            return _lDosimetro;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tipoDosimetro"></param>
        /// <returns></returns>
        public List<Dosimetro> getDOsimetrosRecibidosByTipoDosimetro(string tipoDosimetro)
        {
            string query = "SELECT	HCD_DOSIM.DFUN_NUM, " +
                            "HCD_DOSIM.DFUN_COD, " +
                            "DF.DFUN_FECHA, " +
                            "HCD_DOSIM.DOSIM_TIPO, " +
                            "F.FUNC_NOM, " +
                            "HCD_DOSIM.Cod_Contrato, " +
                            "HCD_DOSIM.fecha_recepcion " +
                            "FROM HCD_dosimetros HCD_DOSIM " +
                            "INNER JOIN FUNCIONARIO F ON F.FUNC_COD = HCD_DOSIM.FUNC_COD " +
                            "INNER JOIN DOSIM_FUN DF ON DF.DFUN_COD = HCD_DOSIM.DFUN_COD " +
                            "WHERE HCD_DOSIM.devuelto = '1' " +
                            "AND HCD_DOSIM.daniado = '0' " +
                            "AND HCD_DOSIM.extraviado = '0' " +
                            "AND F.FUNC_VIGENTE = 'S' " +
                            "AND HCD_DOSIM.generadoTXT = 0 " +
                            "AND HCD_DOSIM.DOSIM_TIPO = @tipoDosimetro AND HCD_DOSIM.DFUN_NUM NOT LIKE '333%'";
            DataTable tabla = Conexion.getSelectTable(query, tipoDosimetro);

            List<Dosimetro> _lDosimetro = new List<Dosimetro>();
            foreach (DataRow fila in tabla.Rows)
            {
                Dosimetro d = new Dosimetro();
                d.codigoLogicoDosimetro = Convert.ToInt32(fila["DFUN_NUM"]);
                d.codigoDosimetro = Convert.ToInt32(fila["DFUN_COD"]);
                d.dfun_fecha = fila["DFUN_FECHA"].ToString();
                d.tipoDosimetro = fila["DOSIM_TIPO"].ToString();
                d.fechaRecepcion = fila["fecha_recepcion"].ToString();
                Contrato c = new Contrato();
                c.codigoContrato = Convert.ToInt32(fila["Cod_Contrato"]);
                d.Contrato = c;

                Funcionario f = new Funcionario();
                f.nombre = fila["FUNC_NOM"].ToString();
                d.Funcionario = f;

                _lDosimetro.Add(d);
            }
            return _lDosimetro;
        }

        /// <summary>
        /// Obtención de la fecha de envio y el tipo de dosimetro en función del id de dosimetro
        /// </summary>
        /// <param name="codigoDosimetro">Codigo de dosimetro</param>
        /// <returns></returns>
        public Dosimetro getFechaEnvioTipoDosimetroAndContratoByCodigoLogicoDosimetro(int codLogico)
        {
            string query = "SELECT TOP 1 DF.DFUN_NUM AS 'CODIGO_LOGICO', " +
                            "DF.DFUN_FUN_COD AS 'CODIGO_UNICO', " +
                            "DF.DFUN_DFECHA AS 'FECHA_ENVIO', " +
                            "DF.DFUN_DOSIM_TIPO AS 'TIPO_DOSIMETRO', " +
                            "C.CONT_COD, " +
                            "DF.DFUN_CONT_COD, " +
                            "C.CONT_DIVISION " +
                            "FROM DOSIM_FUN DF " +
                            "INNER JOIN CONTRATO C ON DF.DFUN_CONT_COD = C.CONT_COD " +
                            "WHERE DF.DFUN_NUM = @codLogico " +
                            "ORDER BY DF.DFUN_FECHA DESC ";


            DataTable ds = Conexion.getSelectTable(query, codLogico);

            Dosimetro d = new Dosimetro();
            List<Contrato> lContratos = new List<Contrato>();

            foreach (DataRow fila in ds.Rows)
            {
                Contrato c = new Contrato();
                c.codigoContrato = Convert.ToInt32(fila["CONT_COD"]);
                c.empresaContratista = fila["CONT_DIVISION"].ToString();
                lContratos.Add(c);

                d.codigoDosimetro = Convert.ToInt32(fila["CODIGO_UNICO"]);
                d.codigoLogicoDosimetro = Convert.ToInt32(fila["CODIGO_LOGICO"]);

                d.fechaEnvio = Convert.ToDateTime(fila["FECHA_ENVIO"]);
                d.tipoDosimetro = fila["TIPO_DOSIMETRO"].ToString();
            }
            d.lContrato = lContratos;
            return d;
        }


        /// <summary>
        /// Obtención de la fecha de envio y el tipo de dosimetro en función del id de dosimetro
        /// </summary>
        /// <param name="codigoDosimetro">Codigo de dosimetro</param>
        /// <returns></returns>
        public Dosimetro getFechaEnvioAndTipoDosimetroByIDDosimetro(int codigoDosimetro)
        {
            string query = "select max(df.dfun_dfecha) as 'fecha_envio', " +
                            "DF.dfun_fun_cod as 'codigo_dosim', " +
                            "DF.DFUN_DOSIM_TIPO as 'tipo_dosimetro' " +
                            "FROM DOSIM_FUN DF " +
                            "INNER JOIN FUNCIONARIO F ON F.FUNC_RUT = DF.DFUN_FUN_RUT " +
                            "INNER JOIN CONT_FUN CF ON CF.CF_FUN_COD = F.FUNC_COD " +
                            "INNER JOIN CONTRATO C ON C.CONT_COD = CF_CONT_COD " +
                            "WHERE DF.dfun_fun_cod = @codDosimetro AND CF.CF_VIGENTE = 'S'" +
                            "GROUP BY df.dfun_fun_cod, DF.DFUN_DOSIM_TIPO";

            DataTable ds = Conexion.getSelectTable(query, codigoDosimetro);

            Dosimetro d = new Dosimetro();
            foreach (DataRow fila in ds.Rows)
            {
                d.codigoDosimetro = Convert.ToInt32(fila["codigo_dosim"]);
                d.fechaEnvio = Convert.ToDateTime(fila["fecha_envio"]);
                d.tipoDosimetro = fila["tipo_dosimetro"].ToString();
            }
            return d;
        }

        /// <summary>
        /// Metodo que obtiene el id del contrato activo (el de mayor fecha) que se encuentra asociado el dosimetro
        /// </summary>
        /// <param name="codigoDosimetro">Codigo unico de dosimetro ()</param>
        /// <returns></returns>
        public Contrato getIdContratoByIDDosimetro(int codigoDosimetro)
        {
            string query = "SELECT MAX(C.CONT_FECHA) AS 'fecha_contrato', " +
                            "C.CONT_COD AS codigo_contrato " +
                            "FROM CONTRATO C " +
                            "INNER JOIN CONT_FUN CF ON CF.CF_CONT_COD = C.CONT_COD " +
                            "INNER JOIN FUNCIONARIO F ON F.FUNC_COD = CF.CF_FUN_COD " +
                            "INNER JOIN DOSIM_FUN DF ON DF.DFUN_FUN_RUT = F.FUNC_RUT " +
                            "WHERE DF.DFUN_FUN_COD = @codDosimetro AND CF.CF_VIGENTE = 'S'" +
                            "GROUP BY CONT_FECHA, C.CONT_COD " +
                            "ORDER BY C.CONT_FECHA DESC ";

            //Obtengo las maximas fechas con sus id contrato
            DataTable ds = Conexion.getSelectTable(query, codigoDosimetro);
            List<Contrato> lContrato = new List<Contrato>();

            foreach (DataRow fila in ds.Rows)
            {
                Contrato c = new Contrato();
                c.codigoContrato = Convert.ToInt32(fila["codigo_contrato"]);
                c.fechaContrato = StringToDateTime(fila["fecha_contrato"].ToString());
                lContrato.Add(c);
            }

            return lContrato[0];
        }

        /// <summary>
        /// Obtencion de los dosimetros que fueron enviados en una fecha y con cierto codigo de Contrato desde la base de datos (no en la tabla HCDDosimetro)
        /// </summary>
        /// <param name="codigoContrato">codigo contrato</param>
        /// <param name="fechaEnvio">fechaEnvio a cliente</param>
        /// <returns></returns>
        public List<Dosimetro> getDosimetrosRelacionadosByCodigoContratoAndFechaEnvioDosim(int codigoContrato, string fechaEnvio)
        {
            string query = "SELECT DF.DFUN_COD, " +
                            "DF.DFUN_NUM, " +
                            "DF.DFUN_FUN_COD, " +
                            "DF.DFUN_DOSIM_TIPO, " +
                            "C.CONT_COD, " +
                            "C.CONT_DIVISION, " +
                            "df.DFUN_FUN_RUT, " +
                            "f.FUNC_NOM " +
                            "FROM DOSIM_FUN DF " +
                            "INNER JOIN CONTRATO C ON DF.DFUN_CONT_COD = C.CONT_COD " +
                            "INNER JOIN FUNCIONARIO F ON F.FUNC_RUT = DF.DFUN_FUN_RUT " +
                            "WHERE  DF.DFUN_CONT_COD = @codigoContrato " +
                            "AND DF.DFUN_DFECHA = @fechaEnvio AND F.FUNC_VIGENTE = 'S' ";

            DataTable tabla = Conexion.getSelectTable(query, codigoContrato, fechaEnvio);
            List<Dosimetro> lDosimetros = new List<Dosimetro>();

            foreach(DataRow fila in tabla.Rows)
            {
                Dosimetro d = new Dosimetro();
                Funcionario f = new Funcionario();
                d.codigoLogicoDosimetro = Convert.ToInt32(fila["DFUN_NUM"]);
                d.id = Convert.ToInt32(fila["DFUN_COD"]);
                d.tipoDosimetro = fila["DFUN_DOSIM_TIPO"].ToString();
                f.nombre = fila["FUNC_NOM"].ToString();
                f.rut = fila["DFUN_FUN_RUT"].ToString();
                f.id = Convert.ToInt32(fila["DFUN_FUN_COD"]);
                d.Funcionario = f;
                lDosimetros.Add(d);
            }
            return lDosimetros;
        }

        /// <summary>
        /// Obtención de la Data (Fecha de envio, tipo dosimetro, contrato)
        /// </summary>
        /// <param name="idDosimetro"></param>
        /// <returns></returns>
        public Contrato getDataDosimetroAndDataContratoByIdDosimetro(int idDosimetro)
        {
            Dosimetro d;
            Contrato c = null;
            try
            {
                c = getIdContratoByIDDosimetro(idDosimetro);
                d = getFechaEnvioAndTipoDosimetroByIDDosimetro(idDosimetro);
                List<Dosimetro> lDosim = new List<Dosimetro>();
                lDosim.Add(d);
                c.lDosimetros = lDosim;
            }
            catch (Exception e)
            {

            }
            return c;
        }

        /// <summary>
        /// Obtención de las funcionalidades asociadas a un usuario
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public List<Sistema> GetFuncionalidades(string userName)
        {
            string query = "SELECT Distinct S.* FROM USUARIO US " +
                            "INNER JOIN roles R ON R.id = US.ROL_ID " +
                            "INNER JOIN roles_funcionalidades RF ON RF.rol_id = R.id " +
                            "INNER JOIN funcionalidades F ON F.id = RF.funcionalidad_id " +
                            "INNER JOIN sistemas S ON S.id = F.sistema_id " +
                            "WHERE F.visible = 1 AND R.vigencia = 1 AND US.VIGENCIA = 1 " +
                            "AND US.US_LOGIN = @userName " +
                            "ORDER BY S.nombre ASC ";

            DataTable tabla = Conexion.getSelectTable(query, userName);
            List<Sistema> lSistemas = new List<Sistema>();
            foreach (DataRow fila in tabla.Rows)
            {
                Sistema s = new Sistema();
                s.id = Convert.ToInt32(fila["id"]);
                s.nombre = fila["nombre"].ToString();
                lSistemas.Add(s);
            }
            return lSistemas;
        }

        public List<Funcionalidad> getFuncionalidadesByUserNameAndSistemaID(string userName, int sistemaId)
        {
            string query = "SELECT f.* FROM USUARIO u " +
                            "INNER JOIN roles r on u.ROL_ID = r.id " +
                            "INNER JOIN roles_funcionalidades rf ON rf.rol_id = r.id " +
                            "inner JOIN funcionalidades f ON f.id = rf.funcionalidad_id " +
                            "where f.funcionalidad_id_padre is null AND f.visible = 1 AND r.vigencia = 1 " +
                            "and u.VIGENCIA = 1 and u.US_LOGIN = @username AND f.sistema_id = @sistemaID ";
            DataTable tabla = Conexion.getSelectTable(query, userName, sistemaId);
            List<Funcionalidad> lFuncionalidades = new List<Funcionalidad>();
            foreach(DataRow fila in tabla.Rows)
            {
                Funcionalidad f = new Funcionalidad();
                f.id = (Int32) fila["id"];
                f.accion = fila["accion"].ToString();
                f.controlador = fila["controlador"].ToString();
                f.funcionalidadIdPadre = fila["funcionalidad_id_padre"].Equals(DBNull.Value) ? 0 : (Int32)fila["funcionalidad_id_padre"];
                f.orden = (Int32) fila["orden"];
                f.pathImagen = fila["path_imagen"].ToString();
                f. sistemaID = (Int32)fila["sistema_id"];
                f.titulo = fila["titulo"].ToString();
                f.visible = (bool) fila["visible"];

                lFuncionalidades.Add(f);
            }
            return lFuncionalidades;
        }
    
        public int checkExistenciaInHCDDosimetroByCodContratoAndFechaEnvio(string codigoContrato, string fechaEnvio)
        {
            string query = "SELECT COUNT(*) cant FROM HCD_dosimetros HD " +
                            "WHERE HD.Cod_Contrato = @codContrato AND HD.Fecha_Envio_Dosimetro = @fechaEnvio ";
            DataTable tabla = Conexion.getSelectTable(query, codigoContrato, fechaEnvio);
            int num = 0;

            foreach(DataRow fila in tabla.Rows)
            {
                num = Convert.ToInt32(fila["cant"]);
            }
            return num;
        }
        public List<HCDDosimetro> getAllDataHCDDosimByCodigoContratoAndFechaEnvio(int codigoContrato, string fechaEnvio)
        {
            string query = "SELECT * FROM HCD_dosimetros HD " +
                            "WHERE HD.Cod_Contrato = @codContrato AND HD.Fecha_Envio_Dosimetro = @fechaEnvio";
            DataTable tabla = Conexion.getSelectTable(query, codigoContrato, fechaEnvio);
            List<HCDDosimetro> _lHCDDosim = new List<HCDDosimetro>();

            foreach (DataRow fila in tabla.Rows)
            {
                HCDDosimetro _hcdDosim = new HCDDosimetro();
                _hcdDosim.codigoUnicoDosim = Convert.ToInt32(fila["DFUN_COD"]);
                _hcdDosim.NDosimetro = Convert.ToInt32(fila["DFUN_NUM"]);
                _hcdDosim.codContrato = Convert.ToInt32(fila["Cod_Contrato"]);
                _hcdDosim.fechaEnvioDosimetro = fila["Fecha_Envio_Dosimetro"].ToString();
                _hcdDosim.Devuelto = Convert.ToBoolean(fila["devuelto"]);
                _hcdDosim.Daniado = Convert.ToBoolean(fila["daniado"]);
                _hcdDosim.Extraviado = Convert.ToBoolean(fila["extraviado"]);
                _hcdDosim.SinUso = Convert.ToBoolean(fila["sin_uso"]);
                _hcdDosim.Reasignado = Convert.ToBoolean(fila["reasignado"]);
                _hcdDosim.FinControl = Convert.ToBoolean(fila["fin_control"]);
                _hcdDosim.Observacion = fila["observacion"].ToString();
                _hcdDosim.HCDID = Convert.ToInt32(fila["HCD_id"]);
                _hcdDosim.TipoDosimetro = fila["DOSIM_TIPO"].ToString();
                
                //Busqueda de funcionario en funcion del cod_funcionario guardado en HCD_Dosimetros
                Funcionario _funcionario = getFuncionarioByCodFuncionario(Convert.ToInt32(fila["FUNC_COD"]));
                _hcdDosim.nombreFuncionario = _funcionario.nombre;
                _hcdDosim.func_cod = _funcionario.id;
                _lHCDDosim.Add(_hcdDosim);
            }
            return _lHCDDosim;

        }

        public Funcionario getFuncionarioByCodFuncionario(int funcCod)
        {
            string query = "SELECT * FROM FUNCIONARIO WHERE FUNC_COD = @codFuncionario";
            DataTable table = Conexion.getSelectTable(query, funcCod);

            Funcionario _funcionario = new Funcionario();
            foreach(DataRow fila in table.Rows)
            {
                _funcionario.id = Convert.ToInt32(fila["FUNC_COD"]);
                _funcionario.rut = fila["FUNC_RUT"].ToString();
                _funcionario.nombre = fila["FUNC_NOM"].ToString();
            }
            return _funcionario;

        }

        //public List<HCDDosimetro> getDataHCDDosimReasignadoByCodDosimetro(int codigoDosimetro)
        //{
        //    string query = "
        //}

        /// <summary>
        /// Metodo que retorna todos los dosimetros que se encuentran reasignados en funcion de la fecha de envio y codigo de contrato
        /// </summary>
        /// <param name="codigoContrato"></param>
        /// <param name="fechaEnvio"></param>
        /// <returns></returns>
        public List<HCDDosimetro> getDataHCDDosimReasignadoByCodDosim(int codDosimetro)
        {
            string query = "SELECT * FROM HCD_dosimetros HCDosim " +
                            "WHERE HCDosim.reasignado = 1 AND DFUN_NUM = @codDosimetro ";
            DataTable tabla = Conexion.getSelectTable(query, codDosimetro);
            List<HCDDosimetro> _lHCDDosim = new List<HCDDosimetro>();

            foreach (DataRow fila in tabla.Rows)
            {
                HCDDosimetro _hcdDosim = new HCDDosimetro();
                _hcdDosim.codigoUnicoDosim = Convert.ToInt32(fila["DFUN_COD"]);
                _hcdDosim.NDosimetro = Convert.ToInt32(fila["DFUN_NUM"]);
                _hcdDosim.codContrato = Convert.ToInt32(fila["Cod_Contrato"]);
                _hcdDosim.fechaEnvioDosimetro = fila["Fecha_Envio_Dosimetro"].ToString();
                _hcdDosim.Devuelto = Convert.ToBoolean(fila["devuelto"]);
                _hcdDosim.Daniado = Convert.ToBoolean(fila["daniado"]);
                _hcdDosim.Extraviado = Convert.ToBoolean(fila["extraviado"]);
                _hcdDosim.SinUso = Convert.ToBoolean(fila["sin_uso"]);
                _hcdDosim.Reasignado = Convert.ToBoolean(fila["reasignado"]);
                _hcdDosim.FinControl = Convert.ToBoolean(fila["fin_control"]);
                _hcdDosim.Observacion = fila["observacion"].ToString();
                _hcdDosim.HCDID = Convert.ToInt32(fila["HCD_id"]);
                _hcdDosim.TipoDosimetro = fila["DOSIM_TIPO"].ToString();
                //Busqueda de funcionario en funcion del cod_funcionario guardado en HCD_Dosimetros
                Funcionario _funcionario = getFuncionarioByCodFuncionario(Convert.ToInt32(fila["FUNC_COD"]));
                _hcdDosim.nombreFuncionario = _funcionario.nombre;
                _hcdDosim.func_cod = _funcionario.id;

                //Se debe cambiar al contrato dinamico, descomentar la siguiente linea
                // _hcdDosim.encargadosContratoEmpresa = getEncargadosContratosByCodContrato(codigoContrato);
                List<Encargados> lEncargados = getEncargadosContratosByCodContrato(901);
                _hcdDosim.correoEncargadoEmpresa = lEncargados[0].email;

                _lHCDDosim.Add(_hcdDosim);
            }
            return _lHCDDosim;
        }

        /// <summary>
        /// Metodo que retorna todos los dosimetros que se encuentran reasignados en funcion de la fecha de envio y codigo de contrato
        /// </summary>
        /// <param name="codigoContrato"></param>
        /// <param name="fechaEnvio"></param>
        /// <returns></returns>
        public List<HCDDosimetro> getDataHCDDosimReasignadosByFechaEnvioAndCodContrato(int codigoContrato, string fechaEnvio)
        {
            string query = "SELECT * FROM HCD_dosimetros HCDosim " +
                            "WHERE HCDosim.reasignado = 1 " +
                            "AND HCDosim.Cod_Contrato = @codContrato " +
                            "AND HCDosim.Fecha_Envio_Dosimetro = @fechaEnvioDosim";

            DataTable tabla = Conexion.getSelectTable(query, codigoContrato.ToString(), fechaEnvio);
            List<HCDDosimetro> _lHCDDosim = new List<HCDDosimetro>();

            foreach(DataRow fila in tabla.Rows)
            {
                HCDDosimetro _hcdDosim = new HCDDosimetro();
                _hcdDosim.codigoUnicoDosim = Convert.ToInt32(fila["DFUN_COD"]);
                _hcdDosim.NDosimetro = Convert.ToInt32(fila["DFUN_NUM"]);
                _hcdDosim.codContrato = Convert.ToInt32(fila["Cod_Contrato"]);
                _hcdDosim.fechaEnvioDosimetro = fila["Fecha_Envio_Dosimetro"].ToString();
                _hcdDosim.Devuelto = Convert.ToBoolean(fila["devuelto"]);
                _hcdDosim.Daniado = Convert.ToBoolean(fila["daniado"]);
                _hcdDosim.Extraviado = Convert.ToBoolean(fila["extraviado"]);
                _hcdDosim.SinUso = Convert.ToBoolean(fila["sin_uso"]);
                _hcdDosim.Reasignado = Convert.ToBoolean(fila["reasignado"]);
                _hcdDosim.FinControl = Convert.ToBoolean(fila["fin_control"]);
                _hcdDosim.Observacion = fila["observacion"].ToString();
                _hcdDosim.HCDID = Convert.ToInt32(fila["HCD_id"]);
                _hcdDosim.TipoDosimetro = fila["DOSIM_TIPO"].ToString();
                //Busqueda de funcionario en funcion del cod_funcionario guardado en HCD_Dosimetros
                Funcionario _funcionario = getFuncionarioByCodFuncionario(Convert.ToInt32(fila["FUNC_COD"]));
                _hcdDosim.nombreFuncionario = _funcionario.nombre;
                _hcdDosim.func_cod = _funcionario.id;

                //Se debe cambiar al contrato dinamico, descomentar la siguiente linea
               // _hcdDosim.encargadosContratoEmpresa = getEncargadosContratosByCodContrato(codigoContrato);
                List<Encargados> lEncargados = getEncargadosContratosByCodContrato(901);
                _hcdDosim.correoEncargadoEmpresa = lEncargados[0].email;

                


                _lHCDDosim.Add(_hcdDosim);
            }
            return _lHCDDosim;
        }


        /// <summary>
        /// Metodo que retorna una lista con los encargados de los contratos con su informacion relacionada
        /// </summary>
        /// <param name="codigoContrato"></param>
        /// <returns></returns>
        public List<Encargados> getEncargadosContratosByCodContrato(int codigoContrato)
        {
            string query = "select * from encargado_contrato " +
                            "where CONT_COD = @codContrato";
            DataTable tabla = Conexion.getSelectTable(query, codigoContrato);

            List<Encargados> _lEncargados = new List<Encargados>();
            foreach(DataRow fila in tabla.Rows)
            {
                Encargados _encargado = new Encargados();
                _encargado.id = Convert.ToInt32(fila["id"]);
                _encargado.contratoCodigo = Convert.ToInt32(fila["CONT_COD"]);
                _encargado.telefono = fila["TELEFONO"].ToString();
                _encargado.nombreEncargado = fila["NOMBRE_ENCARGADO"].ToString();
                _encargado.email = fila["EMAIL"].ToString();
                _encargado.cargo = fila["CARGO"].ToString();
                _lEncargados.Add(_encargado);
            }
            return _lEncargados;
        }

        /// <summary>
        /// Metodo que retorna los dosimetros NO DEVUELTOS de una HCD asociada por codigo de contrato y fecha de envio
        /// </summary>
        /// <param name="codigoContrato"></param>
        /// <param name="fechaEnvio"></param>
        /// <returns></returns>
        public List<HCDDosimetro> getDataHCDDosimByCodigoContratoAndFechaEnvio(int codigoContrato, string fechaEnvio, string devuelto = "0")
        {
            string query = "SELECT * FROM HCD_dosimetros HD " +
                            "WHERE HD.Cod_Contrato = @codContrato AND HD.Fecha_Envio_Dosimetro = @fechaEnvio AND HD.devuelto = @devuelto";

            DataTable tabla = Conexion.getSelectTable(query, codigoContrato, fechaEnvio, devuelto);
            List<HCDDosimetro> _lHCDDosim = new List<HCDDosimetro>();

            foreach(DataRow fila in tabla.Rows)
            {
                HCDDosimetro _hcdDosim = new HCDDosimetro();
                _hcdDosim.codigoUnicoDosim = Convert.ToInt32(fila["DFUN_COD"]);
                _hcdDosim.NDosimetro = Convert.ToInt32(fila["DFUN_NUM"]);
                _hcdDosim.codContrato = Convert.ToInt32(fila["Cod_Contrato"]);
                _hcdDosim.fechaEnvioDosimetro = fila["Fecha_Envio_Dosimetro"].ToString();
                _hcdDosim.Devuelto = Convert.ToBoolean(fila["devuelto"]);
                _hcdDosim.Daniado = Convert.ToBoolean(fila["daniado"]);
                _hcdDosim.Extraviado = Convert.ToBoolean(fila["extraviado"]);
                _hcdDosim.SinUso = Convert.ToBoolean(fila["sin_uso"]);
                _hcdDosim.Reasignado = Convert.ToBoolean(fila["reasignado"]);
                _hcdDosim.FinControl = Convert.ToBoolean(fila["fin_control"]);
                _hcdDosim.Observacion = fila["observacion"].ToString();
                _hcdDosim.HCDID = Convert.ToInt32(fila["HCD_id"]);
                _hcdDosim.TipoDosimetro = fila["DOSIM_TIPO"].ToString();
                //Busqueda de funcionario en funcion del cod_funcionario guardado en HCD_Dosimetros
                Funcionario _funcionario = getFuncionarioByCodFuncionario(Convert.ToInt32(fila["FUNC_COD"]));
                _hcdDosim.func_cod = _funcionario.id;
                _hcdDosim.nombreFuncionario = _funcionario.nombre;
                _lHCDDosim.Add(_hcdDosim);
            }
            return _lHCDDosim;
        }

        /// <summary>
        /// Obtencion de data de usuario en función de username y password
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public List<Usuario> getDataUsuarioByUsuarioAndPassword(string userName, string password)
        {
            string query = "select * from USUARIO U " +
                          "where U.US_LOGIN = @userName and " +
                          "U.US_CLAVE = @password AND " +
                          "U.vigencia = 1";

            DataTable tabla = Conexion.getSelectTable(query, userName, password);

            List<Usuario> lUser = new List<Usuario>();

            foreach (DataRow fila in tabla.Rows)
            {
                Usuario u = new Usuario();
                u.nombre = fila["US_LOGIN"].ToString();
                u.rolId = Convert.ToInt32(fila["ROL_ID"]);
                u.vigencia = Convert.ToBoolean(fila["VIGENCIA"]);
                lUser.Add(u);
            }
            return lUser;
        }
    
        /// <summary>
        /// Obtención de todos los sistemas
        /// </summary>
        /// <returns></returns>
        public List<Sistema> getListOfSistemas()
        {
            string query = "select * from sistemas ";
            DataTable tabla = Conexion.getSelectTable(query);
            List<Sistema> lSistema = new List<Sistema>();

            foreach(DataRow fila in tabla.Rows)
            {
                Sistema s = new Sistema();
                s.id = Convert.ToInt32(fila["id"]);
                s.nombre = fila["nombre"].ToString();
                lSistema.Add(s);
            }
            return lSistema;
        }

        /// <summary>
        /// Obteneción de sistema by id sistema
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Sistema getSistemaById(int id)
        {
            string query = "select * from sistemas " +
                            "where id = @idSistema ";
            DataTable tabla = Conexion.getSelectTable(query, id);
            Sistema s = new Sistema();
            foreach(DataRow fila in tabla.Rows)
            {
                s.id = Convert.ToInt32(fila["id"]);
                s.nombre = fila["nombre"].ToString();
            }
            return s;
        }

        public List<Funcionalidad> getFuncionalidadesByNombreUsuario( string userName )
        {
            string query = "SELECT f.* FROM USUARIO u " +
                            "INNER JOIN roles r on r.id = u.ROL_ID " +
                            "INNER JOIN roles_funcionalidades rf ON rf.rol_id  = r.id " +
                            "INNER JOIN funcionalidades f ON f.id  = rf.funcionalidad_id " +
                            "WHERE u.US_LOGIN = '@userName' ";
            DataTable tabla = Conexion.getSelectTable(query, userName);
            List<Funcionalidad> lFuncionalidades = new List<Funcionalidad>();
            foreach(DataRow fila in tabla.Rows)
            {
                Funcionalidad f = new Funcionalidad();
                f.id = (Int32)fila["id"];
                f.orden = (Int32) fila["orden"];
                f.pathImagen = fila["path_imagen"].ToString();
                f.sistemaID = (Int32)fila["sistema_id"];
                f.titulo = fila["titulo"].ToString();
                f.visible = (bool)fila["visible"];
                f.controlador = fila["controlador"].ToString();
                f.funcionalidadIdPadre = (Int32)fila["funcionalidad_id_padre"];
                f.accion = fila["accion"].ToString();
                lFuncionalidades.Add(f);
            }
            return lFuncionalidades;
        }

        public List<Funcionario> getFuncionarioByContratoAndName(string codigoContrato, string patron)
        {
            string query = "SELECT * FROM FUNCIONARIO F " +
                           "INNER JOIN CONT_FUN CF ON CF.CF_FUN_COD = F.FUNC_COD " +
                           "INNER JOIN CONTRATO C ON CF.CF_CONT_COD = C.CONT_COD " +
                           "WHERE CF.CF_VIGENTE = 'S' AND C.CONT_COD = @codContrato AND F.FUNC_NOM LIKE " + "'%" + patron + "%'";
            DataTable tabla = Conexion.getSelectTable(query, codigoContrato);
            List<Funcionario> _lFuncionario = new List<Funcionario>();
            foreach (DataRow fila in tabla.Rows)
            {
                Funcionario f = new Funcionario();
                f.nombre = fila["FUNC_NOM"].ToString().Trim();
                f.rut = fila["FUNC_RUT"].ToString();
                f.id = Convert.ToInt32(fila["FUNC_COD"]);
                _lFuncionario.Add(f);
            }
            return _lFuncionario;
        }

        /// <summary>
        /// Metodo que retorna los funcionarios por el codigo de contrato y un patron (rut)
        /// </summary>
        /// <param name="codigoContrato"></param>
        /// <param name="patron"></param>
        /// <returns></returns>
        public List<Funcionario> getFuncionarioByContratoAndRut(string codigoContrato, string patron)
        {
            string query = "SELECT * FROM FUNCIONARIO F " +
                           "INNER JOIN CONT_FUN CF ON CF.CF_FUN_COD = F.FUNC_COD " +
                           "INNER JOIN CONTRATO C ON CF.CF_CONT_COD = C.CONT_COD " +
                           "WHERE CF.CF_VIGENTE = 'S' AND C.CONT_COD = @codContrato AND F.FUNC_RUT LIKE " + "'%" + patron + "%'";
           
            //string query = "SELECT * FROM FUNCIONARIO F " +
            //   "INNER JOIN CONT_FUN CF ON CF.CF_FUN_COD = F.FUNC_COD " +
            //   "INNER JOIN CONTRATO C ON CF.CF_CONT_COD = C.CONT_COD " +
            //   "WHERE CF.CF_VIGENTE = 'S' AND C.CONT_COD = @codContrato AND F.FUNC_RUT LIKE '@patron'";
            DataTable tabla = Conexion.getSelectTable(query, codigoContrato);
            List<Funcionario> _lFuncionario = new List<Funcionario>();
            foreach (DataRow fila in tabla.Rows)
            {
                Funcionario f = new Funcionario();
                f.nombre = fila["FUNC_NOM"].ToString().Trim();
                f.rut = fila["FUNC_RUT"].ToString();
                f.id = Convert.ToInt32(fila["FUNC_COD"]);
                _lFuncionario.Add(f);
            }
            return _lFuncionario;
        }

        /// <summary>
        /// MEtodo que retorna los funcionarios activos en funcion del id de contrato
        /// </summary>
        /// <param name="codigoContrato"></param>
        /// <returns></returns>
        public List<Funcionario> getFuncionarioByIdContrato(int codigoContrato)
        {
            string query = "SELECT * FROM FUNCIONARIO F " +
                            "INNER JOIN CONT_FUN CF ON CF.CF_FUN_COD = F.FUNC_COD " +
                            "INNER JOIN CONTRATO C ON CF.CF_CONT_COD = C.CONT_COD " +
                            "WHERE CF.CF_VIGENTE = 'S' AND C.CONT_COD = @codContrato";
            DataTable tabla = Conexion.getSelectTable(query, codigoContrato);
            List<Funcionario> _lFuncionario = new List<Funcionario>();
            foreach(DataRow fila in tabla.Rows)
            {
                Funcionario f = new Funcionario();
                f.nombre = fila["FUNC_NOM"].ToString().Trim();
                f.rut = fila["FUNC_RUT"].ToString();
                _lFuncionario.Add(f);
            }
            return _lFuncionario;
        }

        public Funcionario getFuncionarioByRutAndNombre(int rut, string nombre)
        {
            string query = "SELECT * FROM FUNCIONARIO F " +
                            "WHERE F.FUNC_NOM = @nombreFuncionario AND F.FUNC_RUT = @rut";
            DataTable tabla = Conexion.getSelectTable(query, nombre, rut);
            Funcionario f = new Funcionario();

            foreach(DataRow fila in tabla.Rows)
            {
                f.nombre = fila["FUNC_NOMBRE"].ToString();
                f.rut = fila["FUNC_RUT"].ToString();
            }
            return f;
        }

        public Funcionario getCodeFunctionaryOLD(string codigoUnicoDosimetro, string fechaEnvioDosimetro, string codContrato)
        {
            string query = "SELECT * FROM HCD_dosimetros " +
                            "where Cod_Contrato = @codContrato AND Fecha_Envio_Dosimetro = @fechaEnvio AND DFUN_COD = @codigoUnicoDosim ";

            DataTable tabla = Conexion.getSelectTable(query, codContrato, fechaEnvioDosimetro, codigoUnicoDosimetro);
            Funcionario f = new Funcionario();
            foreach (DataRow fila in tabla.Rows)
            {
               f.id = Convert.ToInt32(fila["FUNC_COD"]);
            }
            return f;
        }

        public Funcionario getDataFuncionariobyCodFuncionario(string codFuncionario)
        {
            string query = "SELECT * FROM FUNCIONARIO WHERE FUNC_COD = @codFuncionario";

            DataTable tabla = Conexion.getSelectTable(query, codFuncionario);

            Funcionario f = new Funcionario();
            foreach (DataRow fila in tabla.Rows)
            {
                f.id = Convert.ToInt32(fila["FUNC_COD"]);
                f.rut = fila["FUNC_RUT"].ToString();
                f.dv = fila["FUNC_DIG"].ToString();
            }
            return f;
        }

        #endregion

        #region inserciones
        public int createtRol(Rol r)
        {
            string query = "INSERT INTO ROLES VALUES ('@nombre', @vigencia)";
            int id = Conexion.insertData(query, r.nombre, r.vigencia);
            return id;
        }

        public int createSistema(Sistema sistema)
        {
            string query = "INSERT INTO sistemas VALUES ('@nombre') ";
            int rowsAffected = Conexion.insertData(query, sistema.nombre);
            return rowsAffected;
        }

        /// <summary>
        /// Crea una nueva HCD 
        /// </summary>
        /// <returns>id de la HCD creada</returns>
        public int createHCD(string comentario = "")
        {
            comentario = "";
            DateTime time = DateTime.Now;
            string query = "INSERT INTO HCD VALUES(@fecha_creacion,@comentario) ";
            Conexion.insertData(query, time, comentario);
            return Conexion.getLastIndexInsertedInIdentityByNameOfTable("HCD");
        }

        /// <summary>
        /// Insercion de registros en HCD_dosimetros
        /// </summary>
        /// <param name="_hcdDosim"></param>
        /// <returns></returns>
        public int createDosimetroHCD(HCDDosimetro _hcdDosim){
        
            string query = "INSERT INTO HCD_dosimetros VALUES(@DFUN_COD, @DFUN_NUM, @dosim_tipo, @Cod_Contrato, @Fecha_Envio_Dosimetro, @HCD_id, @devuelto, @daniado, @extraviado, " +
                           "@sin_uso, @reasignado, @fin_control, @observacion, @fecha_recepcion, @FUNC_COD, 0) ";

            
            int countRowsCreated = Conexion.insertData(query, _hcdDosim.codigoUnicoDosim, _hcdDosim.NDosimetro, _hcdDosim.TipoDosimetro.Trim(), _hcdDosim.codContrato, _hcdDosim.fechaEnvioDosimetro, _hcdDosim.HCDID, _hcdDosim.Devuelto, _hcdDosim.Daniado, _hcdDosim.Extraviado,
                                                _hcdDosim.SinUso, _hcdDosim.Reasignado, _hcdDosim.FinControl, _hcdDosim.Observacion, _hcdDosim.fechaRecepcion, _hcdDosim.func_cod);

           return countRowsCreated;
           
        }

       

        #endregion

        #region update

        public void updateGeneracionTXTHCDDosim(int codUnicoDosim)
        {
            string query = "UPDATE HCD_dosimetros " +
                            "SET generadoTXT = 1 " +
                            "WHERE DFUN_COD = @codigoUnicoDosim ";
            Conexion.updateData(query, codUnicoDosim);   
        }

        /// <summary>
        /// Metodo que realiza el update en la tabla CONT_FUN en el caso de que se realice una reasignacion o un fin de control
        /// Los campos afectados corresponden a CF_VIGENTE = 'N' y se ingresa la fecha CF_FEC_TERM en formato AAAAMMDD
        /// </summary>
        /// <param name="contratoFuncionario"></param>
        /// <param name="fechaTermino"></param>
        public void updateFechaTerminoAndVigencia(int codigoFuncionario, int codigoContrato)
        {
            string query = "UPDATE CONT_FUN " +
                            "SET CF_FEC_TERM = @fechaTermino, " +
                            "CF_VIGENTE = 'N' " +
                            "WHERE CF_CONT_COD = @codigoContrato " +
                            "AND CF_FUN_COD = @codigoFuncionario ";
            string fechaTermino = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
            Conexion.updateData(query, fechaTermino, codigoContrato, codigoFuncionario);
        }


        /// <summary>
        /// Metodo que realiza update en la tabla DOSIM_FUN (Cambia el funcionario antiguo y actualiza al nuevo funcionario)
        /// </summary>
        /// <param name="codigoFuncionario">Codigo Funcionario nuevo</param>
        /// <param name="codigoContrato">Codigo Contrato asociado</param>
        /// <param name="codigoUnicoDosimetro">Codigo unico de dosimetro</param>
        /// <param name="codigoFuncionarioAntiguo">Codigo funcionario para dar de baja</param>
        /// <param name="rutFuncionarioNuevo"></param>
        /// <param name="digVerificadorRutFuncionarioNuevo"></param>
        public void reasingacionDosimetro(string codigoFuncionarioNuevo, string codigoContrato, string codigoUnicoDosimetro, string codigoFuncionarioAntiguo, 
                                            string rutFuncionarioNuevo, string digVerificadorRutFuncionarioNuevo )
        {
            string query = "UPDATE DOSIM_FUN " +
                            "SET DFUN_FUN_RUT = @rutNuevoFuncionario, " +
                            "DFUN_FUN_DIG = @dvNuevoFuncionario, " +
                            "DFUN_FUN_COD = @codigoNuevoFuncionario " +
                            "WHERE DFUN_FUN_COD = @codigoFuncionarioAntiguo " +
                            "AND DFUN_CONT_COD = @codigoContrato " +
                            "AND DFUN_COD = @codigoUnicoDosimetro ";

            Conexion.updateData(query, rutFuncionarioNuevo, digVerificadorRutFuncionarioNuevo, codigoFuncionarioNuevo, codigoFuncionarioAntiguo, codigoContrato, codigoUnicoDosimetro);
        }

        /// <summary>
        /// Metodo que cambia el funcionario asociado en la HCD
        /// </summary>
        /// <param name="codUnicoDosimetro">codigo unico dosimetro DFUN_COD</param>
        /// <param name="codContrato">codigo contrato</param>
        /// <param name="fechaEnvio">fecha envio dosimetro</param>
        /// <param name="codFuncionario">codigo funcionario nuevo</param>
        public void updateHCDDOsimetro(string codUnicoDosimetro, string codContrato, string fechaEnvio, string codFuncionario)
        {
            string query = "UPDATE HCD_dosimetros " +
                            "SET FUNC_COD = @codFuncionario, " +
                            "reasignado = 0 " +
                            "WHERE Cod_Contrato = @codigoContrato " +
                            "AND Fecha_Envio_Dosimetro = @fechaEnvioDosim " +
                            "AND DFUN_COD = @codigoUnicoDosimetro ";
            Conexion.updateData(query, codFuncionario, codContrato, fechaEnvio, codUnicoDosimetro);
        }


        public void changeStateSistema(Sistema s)
        {
            string query = "UPDATE sistemas " +
                            "SET nombre = @nombre " +
                            "where id = @id";
            Conexion.updateData(query, s.nombre, s.id);
            
        }

        public void updateHCDDosimetroSinFechaRecepcion(HCDDosimetro _hcdDosim)
        {
            string query = "UPDATE HCD_dosimetros " +
                           "SET devuelto = @devuelto, " +
                           "daniado = @daniado, " +
                           "extraviado = @extraviado, " +
                           "sin_uso = @sinUso, " +
                           "reasignado = @reasignado, " +
                           "fin_control = @finControl, " +
                           "observacion = @observacion, " +
                           "FUNC_COD = @usuarioAsociado " +
                           "WHERE DFUN_COD = @codigoUnico AND DFUN_NUM = @codigoLogico AND HCD_id = @HCDID " +
                           "AND Fecha_Envio_Dosimetro = @fechaEnvioDosim ";
            Conexion.updateData(query, _hcdDosim.Devuelto, _hcdDosim.Daniado, _hcdDosim.Extraviado, _hcdDosim.SinUso, _hcdDosim.Reasignado, _hcdDosim.FinControl,
                               _hcdDosim.Observacion, _hcdDosim.func_cod, _hcdDosim.codigoUnicoDosim, _hcdDosim.NDosimetro, _hcdDosim.HCDID, _hcdDosim.fechaEnvioDosimetro);            
        }

        public void updateHCDDosimetro(HCDDosimetro _hcdDosim)
        {
            string query = "UPDATE HCD_dosimetros " +
                            "SET devuelto = @devuelto, " +
                            "daniado = @daniado, " +
                            "extraviado = @extraviado, " +
                            "sin_uso = @sinUso, " +
                            "reasignado = @reasignado, " +
                            "fin_control = @finControl, " +
                            "observacion = @observacion, " +
                            "FUNC_COD = @usuarioAsociado, " +
                            "fecha_recepcion = @fechaRecepcion " +
                            "WHERE DFUN_COD = @codigoUnico AND DFUN_NUM = @codigoLogico AND HCD_id = @HCDID " +
                            "AND Fecha_Envio_Dosimetro = @fechaEnvioDosim ";

            Conexion.updateData(query, _hcdDosim.Devuelto, _hcdDosim.Daniado, _hcdDosim.Extraviado, _hcdDosim.SinUso, _hcdDosim.Reasignado, _hcdDosim.FinControl, 
                                _hcdDosim.Observacion,_hcdDosim.func_cod, _hcdDosim.fechaRecepcion, _hcdDosim.codigoUnicoDosim, _hcdDosim.NDosimetro, _hcdDosim.HCDID, _hcdDosim.fechaEnvioDosimetro);            
        }
    
        #endregion

        #region delete
        public void deleteSistemaByid(int id)
        {
            string quey = "DELETE FROM sistemas " +
                            "where id = @id ";
            
        }
        #endregion

        private DateTime StringToDateTime(string d)
        {

            int contador = 0;
            string day = "", month = "", year = "";

            foreach (char c in d)
            {
                if (contador >= 0 && contador <= 3)
                {
                    year += c;
                }
                else if (contador > 3 && contador <= 5)
                {
                    month += c;
                }
                else
                {
                    day += c;
                }
                contador++;
            }

            DateTime date = new DateTime(Convert.ToInt32(year), Convert.ToInt32(month), Convert.ToInt32(day));
            return date;
        }
    }
}